package org.multimedia.jolitorask.extension.calendar;

import java.net.MalformedURLException;
import java.net.URL;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.multimedia.jolitorask.extension.calendar.util.AlarmParser;
import org.multimedia.jolitorask.extension.calendar.util.EventParser;

public class CalendarWrapperSession implements SessionBean {

	private static final long serialVersionUID = 1L;


	@SuppressWarnings("unused")
	private SessionContext context;
	
	/* Parametre */

	
	/* Connexion */
	private static Connection connexionBD;
	private String dbName = "jdbc_asterisk";
	
	private static Connection calendarConnexion;
	private String dbNameCalendar = "jdbc_asterisk";
	
	/* Requetes */
	private static final String insCalendar = "INSERT INTO `ical_calendars` (`id_calendar`, `utilisateur`, `date`, `calscale`, `method`, `prodid`, `version`, `url`) VALUES (NULL, ?, NOW(), NULL, NULL, NULL, NULL, ? )";
	private static final String upCalendar = "UPDATE `ical_calendars` SET `url` = ? WHERE `ical_calendars`.`utilisateur` = ?";
	private static final String delCalendar = "DELETE FROM `ical_calendars` WHERE `ical_calendars`.`utilisateur` = ? ";
	private static final String delCalendar2 = "DELETE FROM `ical_events` WHERE `calendar` = ? ";
	private static final String delCalendar3 = "DELETE FROM `ical_alarms` WHERE `calendar` = ? ";
	
	public void ejbCreate() throws CreateException{
		
		InitialContext ic;
		try {
			
			ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup(dbName);
			connexionBD = ds.getConnection();
			
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	private Connection getCalendarConnexion(){
		
		if( calendarConnexion == null ){
			
			InitialContext ic;
			try {
				
				ic = new InitialContext();
				DataSource ds = (DataSource) ic.lookup(dbNameCalendar);
				calendarConnexion = ds.getConnection();
				
			} catch (NamingException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
		return calendarConnexion;
	}


	/********************************/
	/** Fonctions pour l'EJBObject **/
	/********************************/
	/**
	 * @throws SQLException 
	 */
	private void parseURL( int id_calendar, URL url ) throws SQLException{	
		
		EventParser parser = new EventParser(getCalendarConnexion(), id_calendar, url);
	    parser.parse();
		AlarmParser parser2 = new AlarmParser(getCalendarConnexion(), id_calendar, url);
	    parser2.parse();

	}
	
	public void parseAll() throws SQLException, MalformedURLException{
		
		Statement s = connexionBD.createStatement();
        ResultSet rs = s.executeQuery ( "SELECT `id_calendar`, `url` FROM `ical_calendars`" );

        clean();

        if(rs.next()){
        	
        	int id_cal = rs.getInt( "id_calendar" );
        	
            String lien = rs.getString ( "url" );
            URL url = new URL(lien);
        	parseURL(id_cal, url);
        }

	}
	
	private void clean()throws SQLException {
		
		Statement s = connexionBD.createStatement();
        s.execute( "DELETE FROM `sip_extensions` WHERE `context` = 'alarms' AND `exten` = '999'" );
        s.execute( "TRUNCATE TABLE `ical_events`" );
        s.execute( "TRUNCATE TABLE `ical_alarms`" );
        
        
		s.close();
	}


	public void addCalendar( String context, int id_utilisateur, URL url ) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insCalendar, PreparedStatement.RETURN_GENERATED_KEYS);
		stmt.setInt(1, id_utilisateur);
		stmt.setString(2, url.toString());
		stmt.execute();
		
		int id;
		
		ResultSet ids = stmt.getGeneratedKeys();
		
		while(ids.next()){
			id = ids.getInt(1);
			
			parseURL(id, url);
		}
		
		stmt.close();
		
		
		
	}
	
	public void deleteCalendar( int id_utilisateur ) throws SQLException{
		
		int id_calendar;
		Statement s = connexionBD.createStatement();

        ResultSet rs = s.executeQuery ( "SELECT `id_calendar` FROM `ical_calendars` WHERE `utilisateur` = "+id_utilisateur );


        if(rs.next())
        	id_calendar = rs.getInt ( "id_calendar" );
        else 
        	return;
		
		PreparedStatement stmt = connexionBD.prepareStatement(delCalendar);
		stmt.setInt(1, id_utilisateur);
		stmt.execute();
		stmt.close();
		
		stmt = connexionBD.prepareStatement(delCalendar2);
		stmt.setInt(1, id_calendar);
		stmt.execute();
		stmt.close();
		
		stmt = connexionBD.prepareStatement(delCalendar3);
		stmt.setInt(1, id_calendar);
		stmt.execute();
		stmt.close();
		
	}
	
	public void updateCalendar( int id_utilisateur, URL url ) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(upCalendar);
		stmt.setString(1, url.toString());
		stmt.setInt(2, id_utilisateur);
		stmt.execute();
		stmt.close();
		
	}

	/************************/
	/** Fonctions Heritees **/
	/**    Non utilisees   **/
	/************************/
	public void ejbActivate() throws EJBException, RemoteException{}
	public void ejbPassivate() throws EJBException, RemoteException{}
	public void ejbRemove() throws EJBException, RemoteException{}
	public void setSessionContext(SessionContext sc) throws EJBException, RemoteException {
		context = sc;
	}
	public void unsetEntityContext() throws EJBException, RemoteException {
		context = null;
	}
	
	

}
